package business.database.sqliteMaescorp;

import business.database.filtersemantic.CorpsFilterSQLGen;
import groovy.sql.Sql



/**
 * DAO para la tabla corpfts
 * @author Administrador
 *
 */
public class CorpftsDAO {
	
	private def Sql sqlite;
	
	/**
	* Inicializamos la clase con la instance de SQL
	* @param sql
	*/
   public CorpftsDAO(Sql sql) {
	   sqlite = sql;
   }
   
/**
 * Crea la tabla
 */
	public void create() {
		sqlite.execute("CREATE VIRTUAL TABLE corpfts USING FTS3(idcorp integer, corp text);");
	}
	
	/**
	 * Inserta un corp
	 * @param idcorp
	 * @param corp
	 */
	public void insert(int idcorp, String corp) {
		sqlite.execute("insert into corpfts values (${idcorp},?)", [corp]);
	}
	
	/**
	 * Borra el corp
	 * @param idcorp
	 */
	public void delete(int idcorp) {
		sqlite.execute("delete from corpfts where idcorp = ${idcorp}");
	}
	
	/**
	* Obtiene el maximo idcorp de la tabla
	* @return
	*/
   public int maxidCorp() {
	   String sqlQuery = "Select max(idcorp) as MAXID from corpfts"
	   def max = sqlite.firstRow(sqlQuery)
				   
				   int id
				   if ((max != null) && (max[0] != null))
					   id = max[0] + 1
				   else id = 1
   }
   
	/**
	 * Select idcorp,corp, iso, provincia, instit, organismo, dpto, sector para los corps
	 * que hagan matching en el match especificado.
	 * Closure se encarga de procesar el resutlado
	 * @param match match sobre corps completos
	 */
	public void iterateCorpsAndCentersByMatch(String match,String provcombo, String provfilter, String institcombo, String institfilter, String orgcombo, String orgfilter, String dptocombo, String dptofilter,String sectorcombo, String sectorfilter,Closure closure) {

		CorpsFilterSQLGen sqlfilter = new CorpsFilterSQLGen(match, provcombo, provfilter, institcombo, institfilter, orgcombo, orgfilter, dptocombo, dptofilter,sectorcombo, sectorfilter,"","cfts.corp");
		String where = sqlfilter.getMatch();
		
		String sql = "";
		if (where.contains("match")) {
			sql = "select cfts.idcorp,corp, iso, provincia, instit, organismo, dpto, sector " +
			" from center ce inner join centers_corps cc on ce.idce = cc.idce inner join corpfts cfts on cfts.idcorp = cc.idcorp " + 
			" where ${where}";
		} else {
			sql = "select corp.idcorp,corp, iso, provincia, instit, organismo, dpto, sector " +
			" from center ce inner join centers_corps cc on ce.idce = cc.idce inner join corp on corp.idcorp = cc.idcorp " + 
			" where ${where}";
		}
		
		println sql
	
		sqlite.eachRow(sql,closure);
	  
	}
	
	/**
	* Imprime por consola la seleccion de corps y sus centros que coincidan con el match dado
	*/
   public void printCorpFTSSearch(String match,
		   String provcombo, String provfilter, String institcombo,
		   String institfilter, String orgcombo, String orgfilter,
		   String dptocombo, String dptofilter,String sectorcombo, String sectorfilter) {
	   
	   iterateCorpsAndCentersByMatch(match, provcombo, provfilter, institcombo, institfilter, orgcombo, orgfilter, dptocombo, dptofilter,sectorcombo, sectorfilter, {
	   
		   if (!business.threads.Monitor.getInstance().isOpenSemaphore()) return;
		   
		   business.ViewLogger.getInstance().info("${it[0]} \t\t ${it[2]} ${it[3]} ${it[4]} ${it[5]} ${it[6]} \t\t ${it[1]}")
	   
	   });
   }
	
	
}
